package com.cap.bts.crmdp.dealer.mars.dao.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.inject.Inject;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.cap.bts.crmdp.dealer.mars.dao.IMarsRedemptionDao;
import com.cap.bts.crmdp.dealer.mars.dto.ItemTypeInfoDto;
import com.cap.bts.crmdp.dealer.mars.dto.MarsEligibilityDto;
import com.cap.bts.crmdp.dealer.mars.dto.MarsEligibilityLineDto;
import com.cap.bts.crmdp.dealer.redemption.domain.MemberPackageRedemption;
import com.cap.bts.framework.codemapping.dao.ICodeMappingDao;
import com.cap.bts.framework.codemapping.domain.CodeInfo;
import com.cap.bts.framework.common.dao.callback.NativeQueryBySqlJpaCallback;
import com.cap.bts.framework.common.dao.callback.NativeQueryWithPagingJpaCallback;
import com.cap.bts.framework.common.dao.impl.BaseDaoImpl;
import com.cap.bts.framework.common.exception.AppException;
import com.cap.bts.framework.common.util.Pager;

@Repository("marsRedemptionDao")
public class MarsRedemptionDaoImpl extends BaseDaoImpl<MemberPackageRedemption, Long> implements
    IMarsRedemptionDao {
	
	private static final Logger LOGGER = Logger.getLogger(MarsRedemptionDaoImpl.class);

    @Autowired
    ICodeMappingDao codeMappingDao;

    private static final Map<String, String> MARSTABLE_MAP = new HashMap<String, String>();
    static {
        MARSTABLE_MAP.put("positiongroup", "Position Group Code");
        MARSTABLE_MAP.put("subgroup", "Sub Group Code");
        MARSTABLE_MAP.put("maingroup", "Main Group Code");
    }

    @Inject
    public MarsRedemptionDaoImpl(
        @Value("com.cap.bts.crmdp.dealer.redemption.domain.MemberPackageRedemption") Class<MemberPackageRedemption> clazz) {
        super(clazz);
    }

    public List<ItemTypeInfoDto> getItemTypeInfoListBySql(String sql, Pager pager)
        throws AppException {
        pager.setPageSize(10);
        String countStr = "select count(1) from ('" + sql + "')";
        return getJpaTemplate().executeFind(
            new NativeQueryWithPagingJpaCallback<List<ItemTypeInfoDto>>(sql, countStr, pager));
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Nov 12, 2015
     * @see com.cap.bts.crmdp.dealer.mars.dao.IMarsRedemptionDao#getWeCareItemInfoSqlByItemId(java.lang.Long)
     **/
    @Override
    public List<ItemTypeInfoDto> getWeCareItemInfoByItemId(Long ticketLineid, ItemTypeInfoDto dto)
        throws AppException {
        StringBuilder sqlString = new StringBuilder();
        Integer itemFamily = 0;
        String tableName = "SELECT MAX(c.PARENT_CODE) as table_name "
            + "from tc_code_value c "
            + "where c.CODE_TYPE=(SELECT aa.name from (SELECT * from tm_item_eligibility_def a where a.ticket_def_line_id ='"
            + ticketLineid + "') aa) ";
        String whereCond = "select a.attrName,a.operator,a.attrValue from tm_item_eligibility_def_line a inner join tm_item_eligibility_def b where a.eligibility_id=b.id and b.ticket_def_line_id='"
            + ticketLineid + "' ";
        List<Object[]> tableNames = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<List<Object[]>>(tableName));
        List<Object[]> whereConds = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<List<Object[]>>(whereCond));
        if (tableNames != null && !tableNames.isEmpty()) {
            if ("tm_other_product".equals(tableNames.get(0))) {
                sqlString
                    .append("select a.mfgrcode as code,a.description1,a.description2 from tm_other_product a where 1=1 ");
            } else if ("tm_tyre_product".equals(tableNames.get(0))) {
                sqlString
                    .append("select a.mfgrcode as code,a.description1,a.description2 from tm_tyre_product a where 1=1 ");
            } else if ("tm_service_def".equals(tableNames.get(0))) {
                itemFamily = 1;
                sqlString
                    .append("select a.code,a.name as description1,'' as description2 from tm_service_def a where 1=1 ");
            }
        }
        if (whereConds != null && !whereConds.isEmpty()) {
            for (Object[] obj : whereConds) {
                sqlString.append(" and "
                    + stringOperator2Operator(obj[1].toString(), obj[0].toString(),
                        obj[2].toString()));
            }
        }
        List<ItemTypeInfoDto> itemTypeInfoDtos = new ArrayList<ItemTypeInfoDto>();
        List<Object[]> listDto = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sqlString.toString()));
        if (listDto != null && !listDto.isEmpty()) {
            for (Object[] obj : listDto) {

                ItemTypeInfoDto itemtype = new ItemTypeInfoDto();
                itemtype.setItemId(dto.getItemId());
                itemtype.setTicketId(dto.getTicketId());
                itemtype.setPackageId(dto.getPackageId());
                itemtype.setItemCode((String) obj[0]);
                itemtype.setDescription1((String) obj[1]);
                itemtype.setDescription2((String) obj[2]);
                itemtype.setItemSource(0);
                itemtype.setItemFamily(itemFamily);
                itemtype.setPager(dto.getPager());
                itemTypeInfoDtos.add(itemtype);
            }
        }
        return itemTypeInfoDtos;
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Nov 12, 2015
     * @see com.cap.bts.crmdp.dealer.mars.dao.IMarsRedemptionDao#getWeCareItemInfoSqlByItemId(java.lang.Long)
     **/
    @Override
    public List<ItemTypeInfoDto> getWeCareItemInfoByItemIdWithGroup(Long ticketLineid,
        ItemTypeInfoDto dto) throws AppException {
        StringBuilder sqlString = new StringBuilder();
        List<ItemTypeInfoDto> itemTypeInfoDtos = new ArrayList<ItemTypeInfoDto>();
        Integer itemFamily = 0;
        //取得position名称
        String positionName = "";
        String groupInfo = "SELECT name,category from tm_item_eligibility_def a where a.ticket_def_line_id='"+ ticketLineid + "' ";
        List<Object[]> groupInfos = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<List<Object[]>>(groupInfo));
        for (Object[] groupInfoObj : groupInfos) {
            String colName="";
            if("mainGroup".equals(groupInfos.get(0)[1].toString())){
                colName="main_group";
            }else if("subGroup".equals(groupInfos.get(0)[1].toString())){
                colName="sub_group";
            }else if("positionGroup".equals(groupInfos.get(0)[1].toString())){
                colName="position_group";
            }
            List<String> positionGroupList = codeMappingDao.getPositionGroupList(
                colName, groupInfoObj[0].toString());
            if (positionGroupList != null && !positionGroupList.isEmpty()) {
                positionName = positionGroupList.get(0);
            }
        }

        //设置商品是服务或产品,并生成带Where条件的查询
        String tableName = null;
        List<CodeInfo> codeInfos = codeMappingDao.getCodeInfoByCodeType(positionName);
        if (codeInfos != null && !codeInfos.isEmpty()) {
            for (CodeInfo codeInfo : codeInfos) {
                tableName = codeInfo.getParentCode();
            }
        }
        String whereCond = "select a.attrName,a.operator,a.attrValue from tm_item_eligibility_def_line a inner join tm_item_eligibility_def b where a.eligibility_id=b.id and b.ticket_def_line_id='"
            + ticketLineid + "' ";
        List<Object[]> whereConds = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<List<Object[]>>(whereCond));
        
        if (tableName != null && groupInfos != null && !groupInfos.isEmpty()) {
        	String columnName="";
        	if("mainGroup".equals(groupInfos.get(0)[1].toString())){
        		columnName="MAIN_GROUP";
        	}else if("subGroup".equals(groupInfos.get(0)[1].toString())){
        		columnName="SUB_GROUP";
        	}else if("positionGroup".equals(groupInfos.get(0)[1].toString())){
        		columnName="POSITION_GROUP";
        	}
        	
            if ("tm_other_product".equals(tableName)) {
                sqlString
                    .append("select a.mfgrcode as code,a.description1,a.description2 from tm_other_product a where 1=1"
                    		+ " and " + columnName + " = '" + groupInfos.get(0)[0] + "'");
            } else if ("tm_tyre_product".equals(tableName)) {
                sqlString
                    .append("select a.mfgrcode as code,a.description1,a.description2 from tm_tyre_product a where 1=1 "
                    		+ " and " + columnName + " = '" + groupInfos.get(0)[0] + "'");
            } else if ("tm_service_def".equals(tableName)) {
                itemFamily = 1;
                sqlString
                    .append("select a.code,a.name as description1,'' as description2 from tm_service_def a where 1=1 "
                    		+ " and " + columnName + " = '" + groupInfos.get(0)[0] + "'");
            }
        }
        if (whereConds != null && !whereConds.isEmpty()) {
            for (Object[] obj : whereConds) {
                sqlString.append(" and "
                    + stringOperator2Operator(obj[1].toString(), obj[0].toString(),
                        obj[2].toString()));
            }
        }
        
        LOGGER.error("sqlString:" + sqlString.toString());
        
        if(sqlString.length() > 1){
	        List<Object[]> listDto = getJpaTemplate().executeFind(
	            new NativeQueryBySqlJpaCallback<Object[]>(sqlString.toString()));
	        if (listDto != null && !listDto.isEmpty()) {
	            for (Object[] obj : listDto) {
	                ItemTypeInfoDto itemtype = new ItemTypeInfoDto();
	                itemtype.setItemId(dto.getItemId());
	                itemtype.setTicketId(dto.getTicketId());
	                itemtype.setPackageId(dto.getPackageId());
	                itemtype.setItemCode((String) obj[0]);
	                itemtype.setDescription1((String) obj[1]);
	                itemtype.setDescription2((String) obj[2]);
	                itemtype.setItemSource(0);
	                itemtype.setItemFamily(itemFamily);
	                itemtype.setPager(dto.getPager());
	                itemTypeInfoDtos.add(itemtype);
	            }
	        }
        }
        return itemTypeInfoDtos;
    }

    private String stringOperator2Operator(String operaterStr, String str, String str2) {
        StringBuilder sb = new StringBuilder();
        String strtemp = str;
        String strtemp2 = str2;
        strtemp = marsTableItemToCRMTableItem(strtemp);
        strtemp2 = marsTableItemToCRMTableItem(strtemp2);
        if ("include".equals(operaterStr)) {
            String[] strArray = strtemp2.split("\\|");
            List<String> listStr = new ArrayList<String>(Arrays.asList(strArray));
            sb.append(" (" );
            for (String string : listStr) {
                sb.append(" a." + strtemp);
                sb.append(" like '%" + string + "%' or");
            }
            sb.deleteCharAt(sb.length() - 1);
            sb.deleteCharAt(sb.length() - 1);
            sb.append(" )" );
            return sb.toString();
        }

        if ("notInclude".equals(operaterStr)) {
            String[] strArray = strtemp2.split("\\|");
            List<String> listStr = new ArrayList<String>(Arrays.asList(strArray));
            sb.append(" (" );
            for (String string : listStr) {
                sb.append(" a." + strtemp);
                sb.append(" not like '%" + string + "%' and");
            }
            sb.deleteCharAt(sb.length() - 1);
            sb.deleteCharAt(sb.length() - 1);
            sb.deleteCharAt(sb.length() - 1);
            
            sb.append(" )" );
            return sb.toString();
        }
        
        if ("equal".equals(operaterStr)) {
            sb.append(" a." + strtemp + " in (");
            String[] strArray = strtemp2.split("\\|");
            List<String> listStr = new ArrayList<String>(Arrays.asList(strArray));
            for (String string : listStr) {
                sb.append("'" + string + "',");
            }
            sb.deleteCharAt(sb.length() - 1);
            sb.append(")");
            return sb.toString();
        }

        if ("notEqual".equals(operaterStr)) {
            sb.append(" a." + strtemp + " not in (");
            String[] strArray = strtemp2.split("\\|");
            List<String> listStr = new ArrayList<String>(Arrays.asList(strArray));
            for (String string : listStr) {
                sb.append("'" + string + "',");
            }
            sb.deleteCharAt(sb.length() - 1);
            sb.append(")");
            return sb.toString();
        }
        return operaterStr;
    }

    private String marsTableItemToCRMTableItem(String str) {
        String tempString = str;
        CodeInfo ci = codeMappingDao.getCodeInfoByCodeValue(str);
        if (ci != null) {
            tempString = ci.getCode();
        }
        return tempString;
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Nov 13, 2015
     * @see com.cap.bts.crmdp.dealer.mars.dao.IMarsRedemptionDao#getMarsEligibilityByItemId(java.lang.Long,
     *      com.cap.bts.crmdp.dealer.mars.dto.ItemTypeInfoDto)
     **/
    @Override
    public MarsEligibilityDto getMarsEligibilityByItemId(Long ticketLineid, ItemTypeInfoDto dto)
        throws AppException {
        String tableName = "SELECT MAX(c.PARENT_CODE) as table_name "
            + "from tc_code_value c "
            + "where c.CODE_TYPE=(SELECT aa.name from (SELECT * from tm_item_eligibility_def a where a.ticket_def_line_id ='"
            + ticketLineid + "') aa) ";
        String eligibilitySql = "SELECT a.Category,a.name from tm_item_eligibility_def a where a.ticket_def_line_id ='"
            + ticketLineid + "'";
        String whereCond = "select a.attrName,a.operator,a.attrValue from tm_item_eligibility_def_line a inner join tm_item_eligibility_def b where a.eligibility_id=b.id and b.ticket_def_line_id='"
            + ticketLineid + "' ";
        List<Object[]> tableNames = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<List<Object[]>>(tableName));
        List<Object[]> whereConds = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<List<Object[]>>(whereCond));
        List<Object[]> eligibilitys = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<List<Object[]>>(eligibilitySql));

        MarsEligibilityDto rtnDto = new MarsEligibilityDto();
        if (eligibilitys != null && !eligibilitys.isEmpty()) {
            if (MARSTABLE_MAP.containsKey(eligibilitys.get(0)[0].toString().toLowerCase())) {
                rtnDto.setCategoryType(MARSTABLE_MAP.get(eligibilitys.get(0)[0].toString()
                    .toLowerCase()));
            }
            rtnDto.setCategoryValue(eligibilitys.get(0)[1].toString());
        }

        if (tableNames != null && !tableNames.isEmpty()) {
            if ("tm_other_product".equals(tableNames.get(0))) {
                rtnDto.setItemFamily(0);
            } else if ("tm_tyre_product".equals(tableNames.get(0))) {
                rtnDto.setItemFamily(0);
            } else if ("tm_service_def".equals(tableNames.get(0))) {
                rtnDto.setItemFamily(1);
            }
        }

        List<MarsEligibilityLineDto> eligibilityLineDtos = new ArrayList<MarsEligibilityLineDto>();
        if (whereConds != null && !whereConds.isEmpty()) {
            for (Object[] obj : whereConds) {
                MarsEligibilityLineDto eligibilityLineDto = new MarsEligibilityLineDto();
                eligibilityLineDto.setAttribute(obj[0].toString());
                eligibilityLineDto.setAttrValue(obj[2].toString());
                eligibilityLineDto.setOperator(obj[1].toString());
                eligibilityLineDtos.add(eligibilityLineDto);
            }
        }
        rtnDto.setCriterias(eligibilityLineDtos);
        return rtnDto;
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Nov 13, 2015
     * @see com.cap.bts.crmdp.dealer.mars.dao.IMarsRedemptionDao#getMarsEligibilityByItemId(java.lang.Long,
     *      com.cap.bts.crmdp.dealer.mars.dto.ItemTypeInfoDto)
     **/
    @Override
    public MarsEligibilityDto getMarsEligibilityByItemIdWithGroup(Long ticketLineid,
        ItemTypeInfoDto dto) throws AppException {

        StringBuilder sqlString = new StringBuilder();
        Integer itemFamily = 0;
        //取得position名称
        String itemType = null;
        String groupInfo = "SELECT name,category from tm_item_eligibility_def a where a.ticket_def_line_id = '"+ ticketLineid + "' ";
        List<Object[]> groupInfos = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<List<Object[]>>(groupInfo));
        if(groupInfos!=null&&!groupInfos.isEmpty()) {
            String colName="";
            if("mainGroup".equals(groupInfos.get(0)[1].toString())){
                colName="main_group";
            }else if("subGroup".equals(groupInfos.get(0)[1].toString())){
                colName="sub_group";
            }else if("positionGroup".equals(groupInfos.get(0)[1].toString())){
                colName="position_group";
            }
            List<String> itemTypeLists = codeMappingDao.getItemTypeList(
                colName, groupInfos.get(0)[0].toString());
            if (itemTypeLists != null && !itemTypeLists.isEmpty()) {
                itemType = itemTypeLists.get(0);
            }
        }  
        String eligibilitySql = "SELECT a.Category,a.name from tm_item_eligibility_def a where a.ticket_def_line_id ='"
            + ticketLineid + "'";
        String whereCond = "select a.attrName,a.operator,a.attrValue from tm_item_eligibility_def_line a inner join tm_item_eligibility_def b where a.eligibility_id=b.id and b.ticket_def_line_id='"
            + ticketLineid + "' ";
        List<Object[]> whereConds = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<List<Object[]>>(whereCond));
        List<Object[]> eligibilitys = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<List<Object[]>>(eligibilitySql));

        MarsEligibilityDto rtnDto = new MarsEligibilityDto();
        if (eligibilitys != null && !eligibilitys.isEmpty()) {
            if (MARSTABLE_MAP.containsKey(eligibilitys.get(0)[0].toString().toLowerCase())) {
                rtnDto.setCategoryType(MARSTABLE_MAP.get(eligibilitys.get(0)[0].toString()
                    .toLowerCase()));
            }
            rtnDto.setCategoryValue(eligibilitys.get(0)[1].toString());
        }

        if (itemType==null||"".equals(itemType)){
                rtnDto.setItemFamily(1);
            } else {
                rtnDto.setItemFamily(0);
        }
        List<MarsEligibilityLineDto> eligibilityLineDtos = new ArrayList<MarsEligibilityLineDto>();
        if (whereConds != null && !whereConds.isEmpty()) {
            for (Object[] obj : whereConds) {
                MarsEligibilityLineDto eligibilityLineDto = new MarsEligibilityLineDto();
                eligibilityLineDto.setAttribute(obj[0].toString());
                eligibilityLineDto.setAttrValue(obj[2].toString());
                eligibilityLineDto.setOperator(obj[1].toString());
                eligibilityLineDtos.add(eligibilityLineDto);
            }
        }
        rtnDto.setCriterias(eligibilityLineDtos);
        return rtnDto;
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Nov 17, 2015
     * @see com.cap.bts.crmdp.dealer.mars.dao.IMarsRedemptionDao#getMemberPackageRedemption(java.lang.String)
     **/
    @Override
    public List<MemberPackageRedemption> getMemberPackageRedemptionBySoNumber(String soNumber)
        throws AppException {
        return super.getListByQuery("from MemberPackageRedemption a where a.soNumberRedeem='"
            + soNumber + "'");
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      Nov 19, 2015
     * @see com.cap.bts.crmdp.dealer.mars.dao.IMarsRedemptionDao#getMemberPackageRedemptionByPackageId(java.lang.Long)
    **/
    @Override
    public List<MemberPackageRedemption> getUnUseMemberPackageRedemptionByPackageId(Long packageId)
        throws AppException {
        return super.getListByQuery("from MemberPackageRedemption a where a.status='0' and a.packageId='"
            + packageId + "'");
        
    }

	@Override
	public List<Object[]> getNumberAboutMessage(Long dealerId, String date) throws AppException {
		String sql = //                "-- 当月免费条数 " +
//              "-- 所有门店设定一致月初重置 " +
              "SELECT " +
              " ( " +
              "   SELECT " +
              "    a.code_value " +
              "   FROM " +
              "    tc_code_value a " +
              "   WHERE " +
              "    a. CODE = 'RemindDefaultQuantity' " +
              " ) init, " +
//              " -- 当月充值条数 " +
              " ( " +
              "   SELECT " +
              "    SUM(a.received_quantity * c. CODE) " +
              "   FROM " +
              "    tt_logistic_process_history a " +
              "   INNER JOIN tt_logistic_process b " +
              "   INNER JOIN tc_code_value c " +
              "   WHERE " +
              "    a.logistic_id = b.id " +
              "   AND b.card_def_id = c.ID " +
              "   AND a.receive_date >= date_add( " +
              "    curdate(), " +
              "    INTERVAL - DAY (curdate()) + 1 DAY " +
              "  ) " +
              "   AND a.receive_date <= last_day(curdate()) " +
              "   AND b.dealer_id = " + dealerId +
              " ) recharge, " +
//              " -- 当月发送条数 " +
              " ( " +
              "   SELECT " +
              "    SUM(1) " +
              "   FROM " +
              "    tm_service_remind_send_history a " +
              "   WHERE " +
              "    a.create_date >= date_add( " +
              "     curdate(), " +
              "     INTERVAL - DAY (curdate()) + 1 DAY " +
              "   ) " +
              "   AND a.create_date <= last_day(curdate()) " +
              "   AND a.dealer_id = " + dealerId +
              " ) send, " +
              " ( " +
//              "  -- 剩余总条数 " +
              "   SELECT " +
              "    a.default_quantity + a.purchase_quantity " +
              "   FROM " +
              "    tm_pos_remind_quota a " +
              "   WHERE " +
              "    a.dealer_id = " + dealerId +
              " ) balance ," +
              
// "-- 当日发送条数  " +
 "  (  " +
 "    SELECT  " +
 "      SUM(1)  " +
 "    FROM  " +
 "      tm_service_remind_send_history a  " +
 "    WHERE  " +
 "      DATE_FORMAT(a.create_date, '%Y%m%d') = '" + date + "'" +
 "    AND a.dealer_id = " + dealerId +
 "  ) todaySend " +
              " FROM " +
              "  DUAL";
		return this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<Object[]>(sql));
	}

	@Override
	public List<Object[]> getUnUseMemberPackageRedemptionCount(Long packageRedemptionId) throws AppException {
		String sql = "select count(*) from tm_member_package_redemption a"
				+ " where EXISTS("
					+ "SELECT b.PACKAGE_ID FROM  tm_member_package_redemption  b"
					+ " where a.PACKAGE_ID = b.PACKAGE_ID and b.id  = "+ packageRedemptionId +")"
					+ "  and (a.STATUS = 0  or (a.STATUS = 1 and a.MARS_LOCK_STATUS = 1))";
		return this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<Object[]>(sql));
	}

	@Override
	public List<Object[]> getUnUseMemberPackageRedemptionListByPackageId(Long packageId) throws AppException {
		String sql = "select * from tm_member_package_redemption a"
				+ " where a.PACKAGE_ID = " + packageId
				+ " and (a.STATUS = 0  or (a.STATUS = 1 and a.MARS_LOCK_STATUS = 1))";
		return this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<Object[]>(sql));
	}

}
